﻿#region Using directives

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Diagnostics;

#endregion

using ApplicationLibrary.CounterManager;
using ApplicationLibrary.ExtendedControls;
using ApplicationLibrary.SqlConnector;

namespace ApplicationLibrary.DBManager
{
    #region DBCollection

    public class DBCollection : CollectionBase
    {
        private string name;

        private SqlABDataAdapter dAdapter;
        private List<SqlABParameter> dParam;

        public SqlABDataAdapter DataAdapter { get { return dAdapter; } }

        public SqlABParameter[] Parameter { get { return dParam.ToArray(); } }

        public string Name { get { return name; } }

        public IList list { get { return List; } }

        public DBCollection()
        {
        }

        public DBCollection(string myMaster, SqlABDataAdapter myAdapter, List<SqlABParameter> myParam)
        {
            dAdapter = myAdapter;
            dParam = myParam;
            name = myMaster;
        }

        public void AddMaster(string myMaster, SqlABDataAdapter myAdapter, List<SqlABParameter> myParam)
        {
            dAdapter = myAdapter;
            dParam = myParam;
            name = myMaster;
        }

        public void AddSlave(string mySlave, SqlABDataAdapter myAdapter, List<SqlABParameter> myParam)
        {
            DBCollection myDbCollection = new DBCollection(mySlave, myAdapter, myParam);
            List.Add(myDbCollection);
        }

        public void AddSlave(string mySlaveSlavable, string mySlave, SqlABDataAdapter myAdapter, List<SqlABParameter> myParam)
        {
            DBCollection myDbCollection = new DBCollection(mySlave, myAdapter, myParam);
            RecursiveAdd(mySlaveSlavable, myDbCollection, this.List);
        }

        private void RecursiveAdd(string mySlaveSlavable, DBCollection myDbCollection, IList myList)
        {
            foreach (DBCollection collection in myList)
            {
                if (collection.name == mySlaveSlavable)
                {
                    collection.List.Add(myDbCollection);
                    break;
                }
                if (collection.List.Count > 0)
                    RecursiveAdd(mySlaveSlavable, myDbCollection, collection.List);
            }
        }
    }

    #endregion

    #region DBManager

    public enum DBPosition { First, Middle, Last }

    public enum DBMode { Edit, Browse, Run, Validating, Find }

    public enum DBLock { Lock, Unlock }

    public enum DBOperation { Get, New, Edit, Delete }

    public abstract class DBManager : IDisposable
    {
        public event SqlABRowUpdatingEventHandler OnBeforeRowUpdating;

        #region private variables

        private Table table = null;
        private SqlABConnection conDB = null;
        private SqlABConnection lockDB = null;
        private DataSet dSetDB = null;
        private DBCollection collectionDB = null;
        public ProviderType providerType;

        //private BindingManagerBase	bindingManager  = null;
        private BindingSource masterBinding = null;

        private BindingCollection slaveBindingCollection = null;
        private DBPosition myPositionDB = DBPosition.First;
        private DBMode myModeDB = DBMode.Browse;
        private DBLock myLockDB = DBLock.Unlock;
        private DBOperation dbOperation = DBOperation.Edit;
        private int lastid = -1;
        private IRadarParameters lastKey = null;
        private string myTable = string.Empty;
        private List<CounterControl> fiscalControlList = null;
        private RadarForm myRadarDocument = null;
        private DocumentForm myDocument = null;

        #endregion

        #region protected variables

        #endregion

        #region public DataMemmber

        public iColumn ForeignKey { get; set; }

        public DataSet Dataset { get { return dSetDB; } set { dSetDB = value; } }

        public SqlABConnection DBConnection { get { return conDB; } }

        public DBPosition Position { get { return myPositionDB; } }

        public DBMode Status { get { return myModeDB; } set { myModeDB = value; } }

        public RadarForm RadarDocument { get { return myRadarDocument; } }

        public int Count { get { return masterBinding.Count; } }

        public int Pos { get { return masterBinding.Position; } }

        public int LastID { get { return lastid; } }

        public string MasterTable { get { return myTable; } }

        public IRadarParameters LastKey { set { lastKey = value; } get { return lastKey; } }

        public bool isLocked
        {
            get
            {
                if (lastKey != null)
                {
                    CheckIfLocked(lastKey.GetLockKey());
                    return myLockDB == DBLock.Lock;
                }
                else
                    return true;
            }
        }

        public bool isChanged
        {
            get
            {
                if (dSetDB == null) return false;
                return dSetDB.GetChanges() != null;
            }
        }

        public BindingSource MasterBinding { get { return masterBinding; } }

        public BindingSource SlaveBinding(string text)
        {
            return slaveBindingCollection[text];
        }

        //public CurrencyManager		currManager =	 null;
        public bool SilentMode = false;

        public Table Table
        {
            set { table = value; myTable = value.Tablename; ForeignKey = value.ForeignKey; }
            get { return table; }
        }

        public void BindCounter(ref CounterControl fiscalnocontrol)
        {
            if (fiscalControlList == null)
                fiscalControlList = new List<CounterControl>();

            fiscalControlList.Add(fiscalnocontrol);
        }

        #endregion

        #region Protected virtual method

        protected abstract string CreateMasterQuery();

        protected virtual string CreateSlaveQuery(string name)
        {
            return string.Empty;
        }

        protected virtual List<SqlABParameter> CreateMasterParam()
        {
            return null;
        }

        protected virtual List<SqlABParameter> CreateSlaveParam(string name)
        {
            return null;
        }

        protected virtual List<SqlABParameter> CreateSlaveParam(string name, string slavename)
        {
            return null;
        }

        protected virtual void dAdapter_MasterRowUpdating(object sender, SqlABRowUpdatingEventArgs e)
        {
            if (OnBeforeRowUpdating != null)
                OnBeforeRowUpdating(sender, e);

            if (e.StatementType == StatementType.Insert)
                lastKey = myRadarDocument.GetRadarParameters(e.Row[ForeignKey.Name].ToString());
        }

        protected virtual void dAdapter_MasterRowUpdated(object sender, SqlABRowUpdatedEventArgs e)
        {
            // Scrounger management
            if (myDocument.ScroungerList != null)
                foreach (Scrounger scr in myDocument.ScroungerList)
                    scr.MasterRowUpdated(this, e);
        }

        protected virtual void dAdapter_RowUpdating(object sender, SqlABRowUpdatingEventArgs e)
        {
            // Scrounger management
            if (myDocument.ScroungerList != null)
                foreach (Scrounger scr in myDocument.ScroungerList)
                    scr.RowUpdating(this, e);
        }

        protected virtual bool OnEdit()
        {
            return true;
        }

        protected virtual bool OnBeforeAddNew()
        {
            return true;
        }

        protected virtual bool OnAfterAddNew()
        {
            return true;
        }

        protected virtual bool OnBeforeSave()
        {
            return true;
        }

        protected virtual bool OnBeforeDelete()
        {
            return true;
        }

        protected virtual bool OnAfterSave()
        {
            return true;
        }

        protected virtual bool OnAfterDelete()
        {
            return true;
        }

        #endregion

        #region Costructor

        /// <summary>
        /// 9
        /// </summary>
        /// <param name="name"></param>
        /// <param name="conString"></param>
        ///
        public DBManager(string name, RadarForm radarDocument, DocumentForm document) // ProviderType providerType, string conString)
        {
            this.providerType = GlobalInfo.DBaseInfo.dbManager.DB_Provider;
            this.myRadarDocument = radarDocument;
            this.myDocument = document;

            conDB = new SqlABConnection();
            dSetDB = new DataSet(name);
            dSetDB.Locale = System.Globalization.CultureInfo.InvariantCulture;
            collectionDB = new DBCollection();
            slaveBindingCollection = new BindingCollection();
            try
            {
                if (conDB.State != ConnectionState.Open)
                    conDB.Open();
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.Message);
            }

            // Create the lock Connection
            lockDB = new SqlABConnection();
            if (lockDB.State != ConnectionState.Open)
                lockDB.Open();
        }

        public void Dispose()
        {
            if (lockDB != null && lockDB.State == ConnectionState.Open)
                lockDB.Close();
        }

        #endregion

        #region Add Relaction

        public DataRelation AddRelation(
                                        string relactionName,
                                        iColumn masterColumn,
                                        iColumn slaveColumn
                                        )
        {
            return AddRelation(
                relactionName,
                masterColumn.TableName, masterColumn.Name,
                slaveColumn.TableName, slaveColumn.Name, true
            );
        }

        public DataRelation AddRelation(
            string relactionName,
            iColumn masterColumn,
            iColumn slaveColumn,
            bool createConstraint
            )
        {
            return AddRelation(
                relactionName,
                masterColumn.TableName, masterColumn.Name,
                slaveColumn.TableName, slaveColumn.Name, createConstraint
            );
        }

        public DataRelation AddRelation(
            string relactionName,
            iColumn[] masterColumn,
            iColumn[] slaveColumn,
            bool createConstraint
            )
        {
            if (dSetDB == null) return null;

            System.Data.DataRelation drDB;
            System.Data.DataColumn[] dcM;
            System.Data.DataColumn[] dcS;

            Debug.Assert(masterColumn.Length == slaveColumn.Length, "AddRelation - disambiguous number column");

            dcM = new DataColumn[masterColumn.Length];
            dcS = new DataColumn[slaveColumn.Length];

            for (int t = 0; t < masterColumn.Length; t++)
            {
                dcM[t] = dSetDB.Tables[masterColumn[t].TableName].Columns[masterColumn[t].Name];
                dcS[t] = dSetDB.Tables[slaveColumn[t].TableName].Columns[slaveColumn[t].Name];
            }

            drDB = new System.Data.DataRelation(relactionName, dcM, dcS, createConstraint);

            dSetDB.Relations.Add(drDB);
            dSetDB.EnforceConstraints = createConstraint;

            AddSlaveBinding(relactionName);

            return drDB;
        }

        /// <summary>
        /// Add a relation between two table
        /// </summary>
        /// <param name="relactionName"></param>
        /// <param name="masterTable"></param>
        /// <param name="masterColumn"></param>
        /// <param name="slaveTable"></param>
        /// <param name="slaveColumn"></param>
        /// <returns></returns>
        public DataRelation AddRelation(
            string relactionName,
            string masterTable, string masterColumn,
            string slaveTable, string slaveColumn,
            bool createConstraint
            )
        {
            if (dSetDB == null) return null;

            System.Data.DataRelation drDB;
            System.Data.DataColumn dc1;
            System.Data.DataColumn dc2;

            // Get the parent and child columns of the two tables.
            dc1 = dSetDB.Tables[masterTable].Columns[masterColumn];
            dc2 = dSetDB.Tables[slaveTable].Columns[slaveColumn];
            drDB = new System.Data.DataRelation(relactionName, dc1, dc2, createConstraint);
            try
            {
                dSetDB.Relations.Add(drDB);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            dSetDB.EnforceConstraints = true;

            AddSlaveBinding(relactionName);

            return drDB;
        }

        #endregion

        #region Add Master & Slave

        /// <summary>
        /// Add a master Table
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sqlCommand"></param>
        /// <param name="createCommand"></param>
        /// <returns></returns>
        public SqlABDataAdapter AddMaster(Form myform, string name, bool createCommand)
        {
            if (collectionDB == null || dSetDB == null) return null;

            List<SqlABParameter> dParam = null;
            SqlABCommand dCommand = CreateMasterCommand(ref dParam);

            SqlABDataAdapter dAdapter = CreateDataAdapter(name, dCommand);
            collectionDB.AddMaster(name, dAdapter, dParam);
            AddMasterBinding(name);

            dAdapter.RowUpdated += new SqlABRowUpdatedEventHandler(dAdapter_MasterRowUpdated);
            dAdapter.RowUpdating += new SqlABRowUpdatingEventHandler(dAdapter_MasterRowUpdating);

            if (createCommand) CreateCommand(dAdapter);
            return dAdapter;
        }

        protected virtual SqlABCommand CreateMasterCommand(ref List<SqlABParameter> dParam)
        {
            string sqlQuery = CreateMasterQuery();

            SqlABCommand dCommand = new SqlABCommand(sqlQuery, conDB);
            dParam = CreateMasterParam();
            if (dParam != null)
                dCommand.Parameters.AddRange(dParam);

            return dCommand;
        }

        //private void AddCurrencyManager(Form myform, DataTable myMasterTable)
        //{
        //    if (myform==null) return;

        //    currManager = (CurrencyManager)myform.BindingContext[dSetDB.Tables[myMasterTable.TableName]];
        //    bindingManager = myform.BindingContext[dSetDB.Tables[myMasterTable.TableName]];
        //}

        private void AddMasterBinding(string tableName)
        {
            Debug.Assert(masterBinding == null);
            masterBinding = new BindingSource(dSetDB, tableName);
            masterBinding.AllowNew = true;
            masterBinding.BindingComplete += new BindingCompleteEventHandler(masterBinding_BindingComplete);
        }

        private void masterBinding_BindingComplete(object sender, BindingCompleteEventArgs e)
        {
            MessageBox.Show(e.BindingCompleteState.ToString());
        }

        /// <summary>
        /// Add a slave table
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sqlCommand"></param>
        /// <param name="createCommand"></param>
        /// <returns></returns>
        public SqlABDataAdapter AddSlave(string name, bool createCommand)
        {
            if (collectionDB == null || dSetDB == null) return null;

            string sqlQuery = CreateSlaveQuery(name);

            // Scrounger management
            if (sqlQuery == string.Empty && myDocument.ScroungerList != null)
                foreach (Scrounger scr in myDocument.ScroungerList)
                {
                    sqlQuery = scr.CreateSlaveQuery(name);
                    if (sqlQuery != string.Empty)
                        break;
                }

            SqlABCommand dCommand = new SqlABCommand(sqlQuery, conDB);

            List<SqlABParameter> dParam = CreateSlaveParam(name);
            if (dParam != null)
                dCommand.Parameters.AddRange(dParam);

            // Scrounger management
            if ((dParam == null || dParam.Count == 0) && myDocument.ScroungerList != null)
                foreach (Scrounger scr in myDocument.ScroungerList)
                {
                    dParam = scr.CreateSlaveParam(name);
                    if (dParam == null || dParam.Count == 0)
                        continue;
                    dCommand.Parameters.AddRange(dParam);
                    break;
                }

            SqlABDataAdapter dAdapter = CreateDataAdapter(name, dCommand);
            collectionDB.AddSlave(name, dAdapter, dParam);

            dAdapter.RowUpdating += new SqlABRowUpdatingEventHandler(dAdapter_RowUpdating);
            if (createCommand) CreateCommand(dAdapter);
            return dAdapter;
        }

        /// <summary>
        /// Add Slave to slave table
        /// </summary>
        /// <param name="slavename"></param>
        /// <param name="name"></param>
        /// <param name="sqlCommand"></param>
        /// <param name="createCommand"></param>
        /// <returns></returns>
        public SqlABDataAdapter AddSlave(string slavename, string name, bool createCommand)
        {
            if (collectionDB == null || dSetDB == null) return null;

            string sqlQuery = CreateSlaveQuery(name);
            SqlABCommand dCommand = new SqlABCommand(sqlQuery, conDB);

            List<SqlABParameter> dParam = CreateSlaveParam(slavename);
            if (dParam != null)
                dCommand.Parameters.AddRange(dParam);

            SqlABDataAdapter dAdapter = CreateDataAdapter(name, dCommand);
            collectionDB.AddSlave(slavename, name, dAdapter, dParam);

            if (createCommand) CreateCommand(dAdapter);
            return dAdapter;
        }

        //void AddAuxSlave()
        //{
        //    slaveBindingCollection.Add(new BindingSource(masterBinding, tableName)).AllowNew = false;
        //}

        public void AddSlaveBinding(string tableName)
        {
            slaveBindingCollection.Add(new BindingSource(masterBinding, tableName)).AllowNew = false;
        }

        #endregion

        #region Moving between Record

        public void MoveFirst()
        {
            if (masterBinding == null) return;

            masterBinding.Position = 0;
            myPositionDB = DBPosition.First;
        }

        public void MoveLast()
        {
            if (masterBinding == null) return;

            masterBinding.Position = masterBinding.Count - 1;
            myPositionDB = DBPosition.Last;
        }

        public void MovePrevious()
        {
            if (masterBinding == null) return;

            if (masterBinding.Position > 0)
                masterBinding.Position--;

            if (masterBinding.Position == 0)
                myPositionDB = DBPosition.First;
            else
                myPositionDB = DBPosition.Middle;
        }

        public void MoveNext()
        {
            if (masterBinding == null) return;

            myPositionDB = DBPosition.Middle;

            if (masterBinding.Position < masterBinding.Count - 1)
                masterBinding.Position++;

            if (masterBinding.Position == masterBinding.Count - 1)
                myPositionDB = DBPosition.Last;
            else
                myPositionDB = DBPosition.Middle;
        }

        #endregion

        #region Find Record

        public bool FindRecord(IRadarParameters key)
        {
            if (key != null)
            {
                lastKey = key;
                dSetDB.Clear();
                return FindRecursiveDataAdapter(key, collectionDB);
            }
            return false;
        }

        #endregion

        #region private functions

        private SqlABDataAdapter CreateDataAdapter(string name, SqlABCommand sqlCommand)
        {
            SqlABDataAdapter dAdapter = null;
            dAdapter = new SqlABDataAdapter(sqlCommand);

            //dAdapter.AcceptChangesDuringFill = true;
            try
            {
                //dAdapter.FillSchema(dSetDB,SchemaType.Mapped, name);
                dAdapter.FillSchema(dSetDB, SchemaType.Source);
                dAdapter.Fill(dSetDB, name);
                DataTable dt = dSetDB.Tables[0];

                //mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                //dAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }

            return dAdapter;
        }

        private void CreateCommand(SqlABDataAdapter dAdapter)
        {
            SqlABCommandBuilder cBuilder = new SqlABCommandBuilder(dAdapter);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            dAdapter.UpdateCommand = cBuilder.GetUpdateCommand();
            dAdapter.InsertCommand = cBuilder.GetInsertCommand();
            dAdapter.DeleteCommand = cBuilder.GetDeleteCommand();
        }

        [Obsolete("Serve ?")]
        private string CreateQuery(string mytable, string myForeignKey)
        {
            return string.Format("SELECT * from {0} WHERE {1} = @p1", mytable, myForeignKey);
        }

        #endregion

        #region Update Record

        public void ValidateControl()
        {
            myModeDB = DBMode.Validating;
            Debug.Assert(masterBinding != null);
            UpdateFiscalControl(dbOperation);
            slaveBindingCollection.EndEdit();
            masterBinding.EndEdit();
        }

        public void Find()
        {
            myModeDB = DBMode.Find;
        }

        public bool AddNew()
        {
            if (masterBinding == null) return false;
            if (!OnBeforeAddNew()) return false;

            dSetDB.Clear();

            masterBinding.CancelEdit();
            masterBinding.AddNew();
            slaveBindingCollection.AllowNew = true;
            myModeDB = DBMode.Edit;
            dbOperation = DBOperation.New;
            UpdateFiscalControl(DBOperation.Get);
            return OnAfterAddNew();
        }

        public void Edit()
        {
            if (!FindRecord(lastKey))
            {
                MessageBox.Show(Properties.Resources.Msg_DocumentNotFound,
                            Properties.Resources.Warning,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Exclamation);
                return;
            }
            try
            {
                slaveBindingCollection.AllowNew = true;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
            dbOperation = DBOperation.Edit;
            myModeDB = DBMode.Edit;
            OnEdit();

            LockRecord(lastKey.GetLockKey());
        }

        public void Undo()
        {
            if (masterBinding == null || dSetDB == null) return;
            myModeDB = DBMode.Browse;
            masterBinding.CancelEdit();
            dSetDB.RejectChanges();

            if (lastKey != null)
            {
                UnLockRecord(lastKey.GetLockKey());
                FindRecord(lastKey);
            }
            slaveBindingCollection.AllowNew = false;
        }

        public bool Save()
        {
            if (masterBinding == null || dSetDB == null || collectionDB == null) return false;
            if (!OnBeforeSave()) return false;
            try
            {
                //UpdateFiscalControl(dbOperation);

                //masterBinding.EndEdit();
                DataSet ds2 = dSetDB.GetChanges();
                if (ds2 != null)
                {
                    if (ds2.HasErrors) MessageBox.Show("Errori");
                    if (ds2.HasChanges())
                    {
                        dSetDB.AcceptChanges();
                        UpdateRecursiveDataAdapter(ds2, collectionDB);
                    }
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "Save");
                return false;
            }
            myModeDB = DBMode.Browse;
            dSetDB.EnforceConstraints = true;

            if (lastKey != null)
            {
                UnLockRecord(lastKey.GetLockKey());
                FindRecord(lastKey);
            }
            slaveBindingCollection.AllowNew = false;

            //dSetDB.Clear();
            return OnAfterSave();
        }

        public void Refresh()
        {
            if (lastKey != null)
                FindRecord(lastKey);
        }

        public bool Delete()
        {
            if (dSetDB == null || collectionDB == null) return false;
            if (!OnBeforeDelete())
                return false;

            UpdateFiscalControl(DBOperation.Delete);

            FindRecord(lastKey);
            dSetDB.Tables[collectionDB.Name].Rows[masterBinding.Position].Delete();

            DataSet ds2 = dSetDB.GetChanges();
            if (ds2 != null)
            {
                // Visto che esiste il delete recursivo, devo solo cancellare la testa
                collectionDB.DataAdapter.Update(dSetDB, collectionDB.Name);
                UpdateRecursiveDataAdapter(dSetDB, collectionDB);
                dSetDB.AcceptChanges();
            }
            slaveBindingCollection.AllowNew = false;
            return OnAfterDelete();
        }

        private void UpdateRecursiveDataAdapter(DataSet dataSet, DBCollection collection)
        {
            try
            {
                collection.DataAdapter.Update(dataSet, collection.Name);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "UpdateRecursiveDataAdapter");
                return;
            }

            foreach (DBCollection slavecollection in collection.list)
                UpdateRecursiveDataAdapter(dataSet, slavecollection);
        }

        private bool FindRecursiveDataAdapter(IRadarParameters key, DBCollection collection)
        {
            bool found = false;
            try
            {
                SetParameters(key, collection);

                // Scrounger management
                if (myDocument.ScroungerList != null)
                    foreach (Scrounger scr in myDocument.ScroungerList)
                        scr.SetParameters(this, key, collection);

                found = collection.DataAdapter.Fill(dSetDB, collection.Name) > 0;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, exc.StackTrace);
            }

            if (found)
                foreach (DBCollection slavecollection in collection.list)
                    FindRecursiveDataAdapter(key, slavecollection);

            return found;
        }

        protected abstract void SetParameters(IRadarParameters key, DBCollection collection);

        private void UpdateFiscalControl(DBOperation operation)
        {
            if (fiscalControlList != null)
                foreach (CounterControl fC in fiscalControlList)
                {
                    fC.UpdateValue(operation);
                    if (operation == DBOperation.Get)
                        fC.IsAutomatic = true;
                }
        }

        #endregion

        #region Column Management

        public DataColumn AddColumn(iColumn column)
        {
            return AddColumn(column.TableName, column.Name, column.ColType);
        }

        public DataColumn AddColumn(string tableName, string columnName, System.Type columnType)
        {
            try
            {
                return dSetDB.Tables[tableName].Columns.Add(columnName, columnType);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                return null;
            }
        }

        public void SetColumn<T>(string tableName, string columnName, int row, T val)
        {
            if (row >= 0 && dSetDB.Tables[tableName].Rows[row].RowState != DataRowState.Deleted)
                dSetDB.Tables[tableName].Rows[row][columnName] = val;
        }

        public void SetColumn<T>(iColumn column, int row, T val)
        {
            if (row >= 0 && dSetDB.Tables[column.TableName].Rows[row].RowState != DataRowState.Deleted)
                dSetDB.Tables[column.TableName].Rows[row][column.Name] = val;
        }

        public T GetColumn<T>(iColumn column)
        {
            return GetColumn<T>(column, 0);
        }

        public T GetColumn<T>(iColumn column, int row)
        {
            if (dSetDB.Tables[column.TableName].Rows.Count < 1)
                return default(T);

            if (dSetDB.Tables[column.TableName].Rows[row].RowState == DataRowState.Deleted)
                return (T)GetColumn<T>(column, row, DataRowVersion.Original);

            if (dSetDB.Tables[column.TableName].Rows[row][column.Name] == System.DBNull.Value)
            {
                if (typeof(T) == typeof(string))
                    return (T)Convert.ChangeType("", typeof(T));
                else
                    return default(T);
            }

            return (T)Convert.ChangeType(dSetDB.Tables[column.TableName].Rows[row][column.Name], typeof(T));
        }

        public T GetColumn<T>(iColumn column, int row, DataRowVersion drs)
        {
            if (dSetDB.Tables[column.TableName].Rows[row][column.Name, drs] == System.DBNull.Value)
                return default(T);

            return (T)Convert.ChangeType(dSetDB.Tables[column.TableName].Rows[row][column.Name, drs], typeof(T));
        }

        public DataTable GetDataTable(string tableName)
        {
            return dSetDB.Tables[tableName];
        }

        public DataColumn GetDataColumn(iColumn column)
        {
            return dSetDB.Tables[column.TableName] == null
                ? null
                : dSetDB.Tables[column.TableName].Columns[column.Name];
        }

        public DataRow GetDataRow(string tableName, int row)
        {
            return dSetDB.Tables[tableName].Rows[row];
        }

        #endregion

        #region Record Lock Management

        private void CheckIfLocked(string key)
        {
            SqlABCommand CM = null;
            SqlABParameter P1 = null;
            SqlABParameter P2 = null;

            myLockDB = DBLock.Unlock;

            P1 = new SqlABParameter("@par1", AM_Locks.TableName);
            P2 = new SqlABParameter("@par2", AM_Locks.LockKey);

            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll(AM_Locks.Name);
            qb.AddFrom(AM_Locks.Name);
            qb.AddCompare(AM_Locks.TableName, P1);
            qb.AddCompare(AM_Locks.LockKey, P2);

            CM = new SqlABCommand(qb.Query, lockDB);

            CM.Parameters.Add(P1);
            CM.Parameters.Add(P2);

            P1.Value = collectionDB.Name;
            P2.Value = key;

            try
            {
                bool retry = true;
                while (retry)
                {
                    SqlABDataReader lockRD = CM.ExecuteReader();
                    if (lockRD.Read())
                    {
                        myLockDB = DBLock.Lock;

                        string message = string.Format
                            (
                            Properties.Resources.Msg_Locked,
                            lockRD.GetValue<string>(AM_Locks.Username),
                            lockRD.GetValue<DateTime>(AM_Locks.LockDate)
                            );

                        retry = MessageBox.Show(message,
                            Properties.Resources.Msg_RecordLock,
                            MessageBoxButtons.RetryCancel,
                            MessageBoxIcon.Question,
                            MessageBoxDefaultButton.Button2) == DialogResult.Retry;
                    }
                    else
                        retry = false;
                    lockRD.Close();
                }
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.StackTrace, exc.Message);
            }
        }

        private void LockRecord(string key)
        {
            SqlABCommand lockCM = null;
            SqlABParameter lockP1 = null;
            SqlABParameter lockP2 = null;
            SqlABParameter lockP3 = null;
            SqlABParameter lockP4 = null;

            string insert = string.Format
                (
                "INSERT INTO {0} ({1}, {2}, {3}, {4} ) VALUES (@p1,@p2,@p3,@p4)",
                AM_Locks.Name,
                AM_Locks.Username,
                AM_Locks.TableName,
                AM_Locks.LockKey,
                AM_Locks.LockDate
                );

            lockCM = new SqlABCommand(insert, lockDB);
            lockP1 = new SqlABParameter("@p1", typeof(string), 16);
            lockP2 = new SqlABParameter("@p2", AM_Locks.Username);
            lockP3 = new SqlABParameter("@p3", AM_Locks.TableName);
            lockP4 = new SqlABParameter("@p4", AM_Locks.LockDate);
            lockCM.Parameters.Add(lockP1);
            lockCM.Parameters.Add(lockP2);
            lockCM.Parameters.Add(lockP3);
            lockCM.Parameters.Add(lockP4);

            lockP1.Value = GlobalInfo.UserInfo.User;
            lockP2.Value = collectionDB.Name;
            lockP3.Value = key;
            lockP4.Value = DateTime.Now;

            try
            {
                lockCM.ExecuteScalar();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message + "\n" + exc.StackTrace, "LockRecord");
            }
        }

        private void UnLockRecord(string key)
        {
            SqlABCommand lockCM = null;
            SqlABParameter p1 = null;
            SqlABParameter p2 = null;
            SqlABParameter p3 = null;

            p1 = new SqlABParameter("@p1", AM_Locks.Username);
            p2 = new SqlABParameter("@p2", AM_Locks.TableName);
            p3 = new SqlABParameter("@p3", AM_Locks.LockKey);

            QueryBuilder qb = new QueryBuilder();
            qb.AddDelete();
            qb.AddFrom(AM_Locks.Name);
            qb.AddCompare(AM_Locks.Username, p1);
            qb.AddCompare(AM_Locks.TableName, p2);
            qb.AddCompare(AM_Locks.LockKey, p3);

            lockCM = new SqlABCommand(qb.Query, lockDB);

            lockCM.Parameters.Add(p1);
            lockCM.Parameters.Add(p2);
            lockCM.Parameters.Add(p3);

            p1.Value = GlobalInfo.UserInfo.User;
            p2.Value = collectionDB.Name;
            p3.Value = key;

            try
            {
                lockCM.ExecuteScalar();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.StackTrace, exc.Message);
            }
        }

        #endregion
    }

    #endregion

    public class LockManager
    {
        private ProviderType providerType;
        private SqlABConnection lockConnection = null;

        public string LockKey { get; set; }

        public LockManager()
        {
            providerType = GlobalInfo.DBaseInfo.dbManager.DB_Provider;

            lockConnection = new SqlABConnection(providerType, GlobalInfo.DBaseInfo.dbManager.DB_ConnectionString);

            if (lockConnection.State != ConnectionState.Open)
                lockConnection.Open();
        }

        #region Record Lock Management

        public bool CheckIfLocked(string tablename, string key)
        {
            SqlABCommand lockCM = null;
            SqlABParameter lockP1 = null;
            SqlABParameter lockP2 = null;

            DBLock myLockDB = DBLock.Unlock;

            QueryBuilder qb = new QueryBuilder();
            qb.AddSelectAll(AM_Locks.Name);
            qb.AddFrom(AM_Locks.Name);
            qb.AddCompare(AM_Locks.TableName, "@p51");
            qb.AddCompare(AM_Locks.LockKey, "@p52");

            lockCM = new SqlABCommand(qb.Query, lockConnection);
            lockP1 = new SqlABParameter("@p51", AM_Locks.TableName);
            lockP2 = new SqlABParameter("@p52", AM_Locks.LockKey);
            lockCM.Parameters.Add(lockP1);
            lockCM.Parameters.Add(lockP2);

            lockP1.Value = tablename;
            lockP2.Value = key;

            try
            {
                bool retry = true;
                while (retry)
                {
                    SqlABDataReader lockRD = lockCM.ExecuteReader();
                    if (lockRD.Read())
                    {
                        myLockDB = DBLock.Lock;

                        string message = string.Format
                            (
                            Properties.Resources.Msg_Locked,
                            lockRD.GetValue<string>(AM_Locks.Username),
                            lockRD.GetValue<DateTime>(AM_Locks.LockDate)
                            );

                        retry = MessageBox.Show(message,
                            Properties.Resources.Msg_RecordLock,
                            MessageBoxButtons.RetryCancel,
                            MessageBoxIcon.Question,
                            MessageBoxDefaultButton.Button2) == DialogResult.Retry;
                    }
                    else
                        retry = false;
                    lockRD.Close();
                }
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.StackTrace, exc.Message);
            }

            return myLockDB == DBLock.Lock;
        }

        public void LockRecord(string tablename, string key)
        {
            SqlABCommand lockCM = null;
            SqlABParameter lockP1 = null;
            SqlABParameter lockP2 = null;
            SqlABParameter lockP3 = null;
            SqlABParameter lockP4 = null;
            SqlABParameter lockP5 = null;

            string insert = string.Format
                (
                "INSERT INTO {0} ({1}, {2}, {3}, {4}, {5} ) VALUES (@p1,@p2,@p3,@p4,@p5)",
                AM_Locks.Name,
                AM_Locks.ComputerName.Name,
                AM_Locks.Username.Name,
                AM_Locks.TableName.Name,
                AM_Locks.LockKey.Name,
                AM_Locks.LockDate.Name
                );

            lockCM = new SqlABCommand(insert, lockConnection);
            lockP1 = new SqlABParameter("@p1", AM_Locks.ComputerName);
            lockP2 = new SqlABParameter("@p2", AM_Locks.Username);
            lockP3 = new SqlABParameter("@p3", AM_Locks.TableName);
            lockP4 = new SqlABParameter("@p4", AM_Locks.LockKey);
            lockP5 = new SqlABParameter("@p5", AM_Locks.LockDate);

            lockCM.Parameters.Add(lockP1);
            lockCM.Parameters.Add(lockP2);
            lockCM.Parameters.Add(lockP3);
            lockCM.Parameters.Add(lockP4);
            lockCM.Parameters.Add(lockP5);

            lockP1.Value = GlobalInfo.ComputerInfo.ComputerName;
            lockP2.Value = GlobalInfo.UserInfo.User;
            lockP3.Value = tablename;
            lockP4.Value = key;
            lockP5.Value = DateTime.Now;

            try
            {
                lockCM.ExecuteScalar();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message + "\n" + exc.StackTrace, "LockRecord");
            }
        }

        public void UnLockRecord(string tablename, string key)
        {
            SqlABCommand lockCM = null;
            SqlABParameter lockP1 = null;
            SqlABParameter lockP2 = null;
            SqlABParameter lockP3 = null;

            string delete = string.Format
                (
                "DELETE FROM {0} WHERE {1} = @p1 AND {2} = @p2 AND {3} = @p3",
                AM_Locks.Name,
                AM_Locks.Username.Name,
                AM_Locks.TableName.Name,
                AM_Locks.LockKey.Name
                );

            lockCM = new SqlABCommand(delete, lockConnection);
            lockP1 = new SqlABParameter("@p1", AM_Locks.Username);
            lockP2 = new SqlABParameter("@p2", AM_Locks.TableName);
            lockP3 = new SqlABParameter("@p3", AM_Locks.LockKey);
            lockCM.Parameters.Add(lockP1);
            lockCM.Parameters.Add(lockP2);
            lockCM.Parameters.Add(lockP3);

            lockP1.Value = GlobalInfo.UserInfo.User;
            lockP2.Value = tablename;
            lockP3.Value = key;

            try
            {
                lockCM.ExecuteScalar();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.StackTrace, exc.Message);
            }
        }

        #endregion
    }
}